#encoding:utf-8
'''
jk409@qq.com  created by 2016-6-30
读写xls
'''
#pip install Flask-Excel
#pip install pyexcel-xls  xlwt #推荐
import xlwt   #写xls
import xlrd   #读xls
#import StringIO #流
from io import StringIO  #str流
from io import BytesIO   #二进制流

class XLS:
    def __init__(self):
        pass

    def save_bio(self, title, data):
        wb = xlwt.Workbook()
        ws = wb.add_sheet(title)
        x = 0  # 行
        for i in data:
            y = 0  # 列
            for n in i:
                if isinstance(n, int) or isinstance(n, float):
                    ws.write(x, y, n)  # 列表
                else:
                    ws.write(x, y, str(n).decode('utf8'))  # 列表
                y += 1
            x += 1
        bio = BytesIO()
        wb.save(bio)
        return bio.getvalue()

    def save_sio(self, title, data):
        wb = xlwt.Workbook()
        ws = wb.add_sheet(title)
        x = 0  # 行
        for i in data:
            y = 0  # 列
            for n in i:
                if isinstance(n, int) or isinstance(n, float):
                    ws.write(x, y, n)  # 列表
                else:
                    ws.write(x, y, str(n).decode('utf-8'))  # 列表
                y += 1
            x += 1
        sio = StringIO.StringIO()
        wb.save(sio)
        return sio.getvalue()

    def write(self,title,data,files):
        wb = xlwt.Workbook()
        ws = wb.add_sheet(title)
        x=0 #行
        for i in data:
            y = 0 #列
            #print i
            for n in i:
                #print x,y,n,i[n]
                #if n=='member_type':
                #    continue
                #ws.write(x, y, i[n])  #字典
                if isinstance(n,int) or isinstance(n,float):
                    ws.write(x, y, n)  # 列表
                else:
                    ws.write(x, y, n.decode('utf8'))   #列表
                y +=1
            x += 1
        wb.save(files)

    def read(self, title, files):
        book = xlrd.open_workbook(files)
        try:
            mysheet = book.sheet_by_name(title)
        except:
            print '打开失败！'
            return 'Erro: open file fail ！'
        print '行数：', mysheet.nrows, '\t', '列数：', mysheet.ncols
        data = []
        for x in range(0, mysheet.nrows):
            temp = []
            for y in range(0, mysheet.ncols):
                if mysheet.cell(x, y).value != None:
                    temp.append(mysheet.cell(x, y).value)
                    # print mysheet.cell(x,y).value
            data.append(temp)
        return data

if __name__=="__main__":
    fls='test.xls'
    xls=XLS()
    data=[{'name':'kkk1','age':2},{'name':'kkk2','age':27}]
    #xls.write('test',data,fls)
    res=xls.read('','dishes_2016-07-11.xls')
    print res
    for i in res:
        print i